Spreadsheet applications or other applications that present data in a grid format may be used to access and format data, e.g., financial data, from data sources for use in generating various reports, e.g., financial reports. In some approaches, a notation, e.g., a formula, may be included in a grid or spreadsheet cell in order to retrieve data from a data source to be displayed in that cell. Further, in some approaches, data from a data source may be imported into a proprietary database so that the spreadsheet application can access the data via a spreadsheet add-in or via specialized formulas.
However, the inventors herein have recognized issues with such approaches that use each notation as an individual data request for a cell. For a medium or large report, thousands of notations may be needed to create a full report, and if there are multiple reporting entities, the number of notations is further multiplied by each entity and different view. This approach can result in reports calculating very slowly, the underlying computer systems supporting the reports can be encumbered by excessive processing requirements, and the user's computer session may be unusable during long calculations.
Furthermore, since there are so many data requests being processed for a single report, the underlying systems may not have the capacity to provide reporting features for many concurrent users or have features that require additional resources, such as detailed security rules, advanced financial segment grouping syntaxes, or routine check and balance procedures to verify the accuracy of the data.
Further still, approaches which import data from a data source into a proprietary database and replace the entire data period imported may increase delays in accessing and updating data, reduce accessibility to data, and increase resource drain.
As another example, existing approaches to using requests per cell may not efficiently summarize the thousands of notations and send them to centralized servers for processing. Instead, the current user's session may have to handle a significant processing load for the calculations and re-perform the calculations at the next calculation. This not only encumbers the user session but for a large company could be needlessly drawing resources from a virtual server environment. Furthermore, without proper methods to use centralized servers for calculation processing, there is limited scalability and features that can be leveraged through the internet.
In order to address the issues outline above, systems and methods are provided to improve the efficiency and productivity of using a remote server to display data in a grid. In one embodiment, a method for managing data in a grid displayed on a client device comprises, responsive to receiving a data request from the client device, returning to a cell or cells in the grid a cached calculation corresponding to the data request if the data request is stored in a cache, and otherwise processing a calculation corresponding to the data request. In this way, a relational database may efficiently handle numerous data requests that would normally be handled by an online analytic processing server.
In another embodiment, a method for managing data in a grid on a client device comprises, responsive to receiving an imported data value from a remote data source, replacing a previously imported data value with the imported data value responsive to a non-zero difference between the imported data value and the previously imported data value. In this way, data processing and synchronization using a relational database is made more efficient and change visibility for client reporting is enabled.
As another embodiment, a system for managing data in a grid comprises: one or more data sources configured to store raw data; a client device configured with a grid application; a grid server configured with a relational database and adapted to service requests from the client device, the grid server configured to store data processed from the raw data in the relational database, the grid server further configured with a cache to store calculations; and an import manager configured to import changes in the raw data from the data storage into the grid server and to store historical values of the raw data. In this way, advanced processing is available to a relational database user while minimally encumbering a user session.